﻿using Treca.Message.Entity;
using Treca.Util;
using EFCore.Sharding;
using Treca.Business;
using System.Collections.Generic;
using System;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Data;
using Microsoft.AspNetCore.Http;
using Treca.Business.Base_SysManage;
using Treca.Util.Service;
using static Treca.Entity.DataRule.EnumType;
using Treca.Message.Services;

namespace Treca.Message.Business
{
    public class base_messageBusiness : BaseBusiness<base_message>, Ibase_messageBusiness, IDependency
    {
        public BaseDataHelper listHelper=new BaseDataHelper();
        ITranslateService TranslateService { get => AutofacHelper.GetScopeService<ITranslateService>(); }

        #region 重写
        public base_messageBusiness(IDataPermission dataPermission,IRepository repository):base(repository)
        {
            _dataPermission = dataPermission;
        }
        IDataPermission _dataPermission { get; }

        public override IQueryable<base_message> GetIQueryable()
        {
            return _dataPermission.GetIQ_DataList<base_message>(Service);
        }

        #endregion

        #region 外部接口

        public DataTable GetDataList(Pagination pagination, Dictionary<string,string> keyValues,bool usedto=true)
        {
            var q = GetIQueryable();
            //筛选
            string param = $@"";
            Dictionary<string, List<string>> keyvalue = listHelper.GetDataKeys(typeof(base_message).FullName, keyValues);
            List<string> keys = new List<string>();
            int i = 0;
            int keynum = 0;
            foreach (var key in keyvalue)
            {
                param += $@"(";

                for (int j = 0; j < key.Value.Count; j++)
                {
                    keys.Add(key.Value[j]);
                    if (j != key.Value.Count - 1)
                        param += $@"{key.Key}.Contains(@{keynum}) or ";
                    else
                        param += $@"{key.Key}.Contains(@{keynum})";
                    keynum++;
                }

                if (i != keyvalue.Count() - 1)
                    param += $@") and ";
                else
                    param += $@")";
                i++;
            }
            //筛选
            if (!param.IsNullOrEmpty() && !keys.IsNullOrEmpty())
                q = q.Where(param, keys.ToArray());
            if(usedto){
                return listHelper.DataTableSelect<base_message>(q.GetPagination(pagination).ToDataTable());
            }else{
                return q.GetPagination(pagination).ToDataTable();
            }
        }
        


        
        public DataTable GetDataTable(Pagination pagination, Dictionary<string,string> keyValues)
        {
              var q = GetIQueryable();
            //筛选
            string param = $@"";
            Dictionary<string, List<string>> keyvalue = listHelper.GetDataKeys(typeof(base_message).FullName, keyValues);
            List<string> keys = new List<string>();
            int i = 0;
            int keynum = 0;
            foreach (var key in keyvalue)
            {
                param += $@"(";

                for (int j = 0; j < key.Value.Count; j++)
                {
                    keys.Add(key.Value[j]);
                    if (j != key.Value.Count - 1)
                        param += $@"{key.Key}.Contains(@{keynum}) or ";
                    else
                        param += $@"{key.Key}.Contains(@{keynum})";
                    keynum++;
                }

                if (i != keyvalue.Count() - 1)
                    param += $@") and ";
                else
                    param += $@")";
                i++;
            }
            //筛选
            if (!param.IsNullOrEmpty() && !keys.IsNullOrEmpty())
                q = q.Where(param, keys.ToArray());
            List<TableInfo> infos = dbHelper.GetDbTableInfo(EnumHelper.GetTableName(typeof(base_message)));
            DataTable dt = new DataTable();
            foreach (var info in infos) {
                if (!typeof(base_message).GetProperty(info.Name).IsNullOrEmpty())
                {
                    info.Description = TranslateService.GetTranslate("",$@"Message_base_message_{info.Name}",info.Description);
                    dt.Columns.Add(info.Description, dbHelper.DbTypeStr_To_CsharpType(info.Type));
                    dt.Columns[info.Description].Caption=info.Description;
                    dt.Columns[info.Description].ExtendedProperties.Add("filed",info.Name);
                }
            }
            foreach (var item in listHelper.DataListSelect(q.ToList()))
            {
                DataRow dataRow = dt.NewRow();
                foreach (var info in infos)
                {
                    if (!typeof(base_message).GetProperty(info.Name).IsNullOrEmpty())
                    {
                        info.Description = TranslateService.GetTranslate("",$@"Message_base_message_{info.Name}",info.Description);
                        dataRow[info.Description] = item.GetPropertyValue(info.Name).IsNullOrEmpty() ? DBNull.Value : item.GetPropertyValue(info.Name);
                    }
                }
                dt.Rows.Add(dataRow);
            }
            return dt;
        }
        

          public DataTable GetDataList(Pagination pagination, List<SearchInfo> searchInfos)
        {
            var q = GetIQueryable();
            //筛选
            string param = $@"";
            searchInfos = listHelper.GetDataKeys(typeof(base_message).FullName, searchInfos);
            List<string> keys = new List<string>();
            SearchHelper.SearchInfoToWhere<base_message>(ref param, ref keys, searchInfos);
            //筛选
            if (!param.IsNullOrEmpty() && !keys.IsNullOrEmpty())
                q = q.Where(param, keys.ToArray());

            return listHelper.DataTableSelect<base_message>(q.GetPagination(pagination).ToDataTable());
        }

        /// <summary>
        /// 获取数据列表
        /// </summary>
        /// <param name="pagination">分页参数</param>
        /// <param name="ComplexQuerys">查询参数树</param>
        /// <returns></returns>
        public DataTable GetDataListByComplexQuery(Pagination pagination, ComplexQuery ComplexQuerys)
        {
            if (ComplexQuerys.IsNullOrEmpty())
            {
                ComplexQuerys = new ComplexQuery();
            }
            var q = GetIQueryable();
            ComplexQuerys = listHelper.GetDataKeys(typeof(base_message).FullName, ComplexQuerys);
            //筛选
            string param = $@"";
            List<string> keys = new List<string>();
            int i = 0;
            param = SearchHelper.SearchInfoToWhere<base_message>(ref keys, ref i, ComplexQuerys);
            //筛选
            if (!param.IsNullOrEmpty() && !keys.IsNullOrEmpty())
                q = q.Where(param, keys.ToArray());

            //筛选
            return listHelper.DataTableSelect<base_message>(q.GetPagination(pagination).ToDataTable());
        }

        public DataTable GetDataListIntersect(IQueryable<base_message> base_messages,Pagination pagination, List<SearchInfo> searchInfos)
        {
            var q = base_messages;
            //筛选
            string param = $@"";
            searchInfos = listHelper.GetDataKeys(typeof(base_message).FullName, searchInfos);
            List<string> keys = new List<string>();
            SearchHelper.SearchInfoToWhere<base_message>(ref param, ref keys, searchInfos);
            //筛选
            if (!param.IsNullOrEmpty() && !keys.IsNullOrEmpty())
                q = q.Where(param, keys.ToArray());

            return listHelper.DataTableSelect<base_message>(q.GetPagination(pagination).ToDataTable());
        }

         public IQueryable<base_message> GetDataListIntersectIqueryable(IQueryable<base_message> base_messages, Pagination pagination, List<SearchInfo> searchInfos)
        {
            var q = base_messages;
            //筛选
            string param = $@"";
            searchInfos = listHelper.GetDataKeys(typeof(base_message).FullName, searchInfos);
            List<string> keys = new List<string>();
            SearchHelper.SearchInfoToWhere<base_message>(ref param, ref keys, searchInfos);
            //筛选
            if (!param.IsNullOrEmpty() && !keys.IsNullOrEmpty())
                q = q.Where(param, keys.ToArray());

            return q.GetPagination(pagination);
        }

         public DataTable GetDataListIntersectExport(IQueryable<base_message> base_messages, Pagination pagination, List<SearchInfo> searchInfos)
        {
            var q = base_messages;
            //筛选
            string param = $@"";
            searchInfos = listHelper.GetDataKeys(typeof(base_message).FullName, searchInfos);
            List<string> keys = new List<string>();
            SearchHelper.SearchInfoToWhere<base_message>(ref param, ref keys, searchInfos);
            //筛选
            if (!param.IsNullOrEmpty() && !keys.IsNullOrEmpty())
                q = q.Where(param, keys.ToArray());

            List<TableInfo> infos = dbHelper.GetDbTableInfo(EnumHelper.GetTableName(typeof(base_message)));
            DataTable dt = new DataTable();
            foreach (var info in infos) {

                if (!typeof(base_message).GetProperty(info.Name).IsNullOrEmpty())
                {
                    info.Description = TranslateService.GetTranslate("",$@"Message_base_message_{info.Name}",info.Description);
                    dt.Columns.Add(info.Description, dbHelper.DbTypeStr_To_CsharpType(info.Type));
                    dt.Columns[info.Description].Caption=info.Description;
                    dt.Columns[info.Description].ExtendedProperties.Add("filed",info.Name);
                }
            }
            foreach (var item in listHelper.DataListSelect(q.ToList()))
            {
                DataRow dataRow = dt.NewRow();
                foreach (var info in infos)
                {
                    if (!typeof(base_message).GetProperty(info.Name).IsNullOrEmpty())
                    {
                        info.Description = TranslateService.GetTranslate("",$@"Message_base_message_{info.Name}",info.Description);
                        dataRow[info.Description] = item.GetPropertyValue(info.Name).IsNullOrEmpty() ? DBNull.Value : item.GetPropertyValue(info.Name);
                    }
                }
                dt.Rows.Add(dataRow);
            }
            return dt;
        }

        public DataTable GetDataListIntersectExport(Pagination pagination, List<SearchInfo> searchInfos)
        {
            var q = GetIQueryable();
            //筛选
            string param = $@"";
            searchInfos = listHelper.GetDataKeys(typeof(base_message).FullName, searchInfos);
            List<string> keys = new List<string>();
            SearchHelper.SearchInfoToWhere<base_message>(ref param, ref keys, searchInfos);
            //筛选
            if (!param.IsNullOrEmpty() && !keys.IsNullOrEmpty())
                q = q.Where(param, keys.ToArray());

            List<TableInfo> infos = dbHelper.GetDbTableInfo(EnumHelper.GetTableName(typeof(base_message)));
            DataTable dt = new DataTable();
            foreach (var info in infos) {

                if (!typeof(base_message).GetProperty(info.Name).IsNullOrEmpty())
                {
                    info.Description = TranslateService.GetTranslate("",$@"Message_base_message_{info.Name}",info.Description);
                    dt.Columns.Add(info.Description, dbHelper.DbTypeStr_To_CsharpType(info.Type));
                    dt.Columns[info.Description].Caption=info.Description;
                    dt.Columns[info.Description].ExtendedProperties.Add("filed",info.Name);
                }
            }
            foreach (var item in listHelper.DataListSelect(q.ToList()))
            {
                DataRow dataRow = dt.NewRow();
                foreach (var info in infos)
                {
                    if (!typeof(base_message).GetProperty(info.Name).IsNullOrEmpty())
                    {
                        info.Description = TranslateService.GetTranslate("",$@"Message_base_message_{info.Name}",info.Description);
                        dataRow[info.Description] = item.GetPropertyValue(info.Name).IsNullOrEmpty() ? DBNull.Value : item.GetPropertyValue(info.Name);
                    }
                }
                dt.Rows.Add(dataRow);
            }
            return dt;
        }
        /// <summary>
        /// 获取数据列表
        /// </summary>
        /// <param name="pagination">分页参数</param>
        /// <param name="ComplexQuerys">查询参数树</param>
        /// <returns></returns>
        public DataTable GetDataListIntersectExport(Pagination pagination, ComplexQuery ComplexQuerys)
        {
            if (ComplexQuerys.IsNullOrEmpty())
            {
                ComplexQuerys = new ComplexQuery();
            }
            var q = GetIQueryable();
            ComplexQuerys = listHelper.GetDataKeys(typeof(base_message).FullName, ComplexQuerys);
            //筛选
            string param = $@"";
            List<string> keys = new List<string>();
            int i = 0;
            param = SearchHelper.SearchInfoToWhere<base_message>(ref keys, ref i, ComplexQuerys);
            //筛选
            if (!param.IsNullOrEmpty() && !keys.IsNullOrEmpty())
                q = q.Where(param, keys.ToArray());

            List<TableInfo> infos = dbHelper.GetDbTableInfo(EnumHelper.GetTableName(typeof(base_message)));
            DataTable dt = new DataTable();
            foreach (var info in infos) {

                if (!typeof(base_message).GetProperty(info.Name).IsNullOrEmpty())
                {
                    info.Description = TranslateService.GetTranslate("",$@"Message_base_message_{info.Name}",info.Description);
                    dt.Columns.Add(info.Description, dbHelper.DbTypeStr_To_CsharpType(info.Type));
                    dt.Columns[info.Description].Caption=info.Description;
                    dt.Columns[info.Description].ExtendedProperties.Add("filed",info.Name);
                }
            }
            foreach (var item in listHelper.DataListSelect(q.ToList()))
            {
                DataRow dataRow = dt.NewRow();
                foreach (var info in infos)
                {
                    if (!typeof(base_message).GetProperty(info.Name).IsNullOrEmpty())
                    {
                        info.Description = TranslateService.GetTranslate("",$@"Message_base_message_{info.Name}",info.Description);
                        dataRow[info.Description] = item.GetPropertyValue(info.Name).IsNullOrEmpty() ? DBNull.Value : item.GetPropertyValue(info.Name);
                    }
                }
                dt.Rows.Add(dataRow);
            }
            return dt;
        }


        public DataTable GetDataListMessageInfo(Pagination pagination, ComplexQuery ComplexQuerys)
        {
            IMessageInfoBusiness stockInfoBusiness = AutofacHelper.GetScopeService<IMessageInfoBusiness>();
            var q = stockInfoBusiness.GetIQueryable();
            if (ComplexQuerys.IsNullOrEmpty())
            {
                ComplexQuerys = new ComplexQuery();
            }
            ComplexQuerys = listHelper.GetDataKeys(typeof(MessageInfo).FullName, ComplexQuerys);
            //筛选
            string param = $@"";
            List<string> keys = new List<string>();
            int i = 0;
            param = SearchHelper.SearchInfoToWhere<MessageInfo>(ref keys, ref i, ComplexQuerys);
            //筛选
            if (!param.IsNullOrEmpty() && !keys.IsNullOrEmpty())
                q = q.Where(param, keys.ToArray());
            return listHelper.DataTableSelect<MessageInfo>(q.GetPagination(pagination).ToDataTable());
        }
        public DataTable GetDataList(string condition, string keyword)
        {
            var q = GetIQueryable();
            //筛选
            string param = $@"";
            List<string> keys = listHelper.GetDataKeys(typeof(base_message).FullName, condition, keyword);
            for (int i = 0; i < keys.Count; i++) {
                if (i != keys.Count - 1)
                    param += $@"{condition}.Contains(@{i}) or ";
                else
                    param += $@"{condition}.Contains(@{i})";
            }
            //筛选
            if (!condition.IsNullOrEmpty() && !keyword.IsNullOrEmpty())
                q = q.Where(param, keys.ToArray());

            List<TableInfo> infos = dbHelper.GetDbTableInfo(EnumHelper.GetTableName(typeof(base_message)));
            DataTable dt = new DataTable();
            foreach (var info in infos) {

                if (!typeof(base_message).GetProperty(info.Name).IsNullOrEmpty())
                {
                    info.Description = TranslateService.GetTranslate("",$@"Message_base_message_{info.Name}",info.Description);
                    dt.Columns.Add(info.Description, dbHelper.DbTypeStr_To_CsharpType(info.Type));
                    dt.Columns[info.Description].Caption=info.Description;
                    dt.Columns[info.Description].ExtendedProperties.Add("filed",info.Name);
                }
            }
            foreach (var item in listHelper.DataListSelect(q.ToList()))
            {
                DataRow dataRow = dt.NewRow();
                foreach (var info in infos)
                {
                    if (!typeof(base_message).GetProperty(info.Name).IsNullOrEmpty())
                    {
                        info.Description = TranslateService.GetTranslate("",$@"Message_base_message_{info.Name}",info.Description);
                        dataRow[info.Description] = item.GetPropertyValue(info.Name).IsNullOrEmpty() ? DBNull.Value : item.GetPropertyValue(info.Name);
                    }
                }
                dt.Rows.Add(dataRow);
            }
            return dt;
        }

        //通过SQL查询数据，适合于多表关联查询或者视图(修改可变参数？)
        public DataTable GetDataTableSql(string system, string Respondents, string ID_card,string Telephone, string Name,string Gate)
        {            
            //system   门岗操作员
            //Respondents  拜访人
            //ID_card 身份证
            //Telephone 电话
            //Name 接待人
            //Gate 门岗

            DataTable list = GetDataTableWithSql($@"select a.Name '姓名',a.Remark  '备注'
                from reserve_information a
                join visit_type_authorize b on a.Visit_units = b.dept
                join base_user c on c.DepartmentId = b.dept
                join base_gate d on b.entry_exit_Gate = d.id
                WHERE ID_card like " + "'" + ID_card + "%' " +
                "and c.UserName like " + "'" + system + "%' " +
                "and a.Name like " + "'" + Respondents + "%' " +
                "and a.Telephone like " + "'" + Telephone + "%' " +
                "and a.Respondents like " + "'" + Name + "%' " +
                "and d.Name like  " + "'" + Gate + "%' " +
                "and date_format(a.Visit_time,'%Y-%m-%d' ) <= date_format(DATE_SUB(curdate(),INTERVAL 0 DAY),'%Y-%m-%d')");

 
            return list;
        }

        // 编号最大值查询
        public string GetMaxNo(string Code, string Table)
        {
            DataTable list = GetDataTableWithSql($@"SELECT MAX(" + Code + ") FROM " + Table+ "; ");
            string str = list.Rows[0][Code].ToString();
            if(str=="")
            {
                str = "000000000000000000000000001";
            }

            return str;
        }
     
        public DataTable GetCodeSql(string Table)
        {
            String sql = "select MapDocFieldName,Prefix,Postfix,SeriationLen,CodeLength from codegenerate WHERE MapTableName = '" + Table + "' ; ";

            DataTable list = GetDataTableWithSql(sql);


            return list;
        }
        
        public BaseDataHelper GetBaseDataHelper()
        {
            return listHelper;
        }

        public DataTable GetDataList()
        {

            var q = GetIQueryable();
            //筛选
                q = q.Take(1);
            List<TableInfo> infos = dbHelper.GetDbTableInfo(EnumHelper.GetTableName(typeof(base_message)));
            DataTable dt = new DataTable();
            foreach (var info in infos) {
                if (!typeof(base_message).GetProperty(info.Name).IsNullOrEmpty())
                {
                    info.Description = TranslateService.GetTranslate("",$@"Message_base_message_{info.Name}",info.Description);
                    dt.Columns.Add(info.Description, dbHelper.DbTypeStr_To_CsharpType(info.Type));
                    dt.Columns[info.Description].Caption=info.Description;
                    dt.Columns[info.Description].ExtendedProperties.Add("filed",info.Name);
                }
            }
            foreach (var item in listHelper.DataListSelect(q.ToList()))
            {
                DataRow dataRow = dt.NewRow();
                foreach (var info in infos)
                {
                    if (!typeof(base_message).GetProperty(info.Name).IsNullOrEmpty())
                    {
                        info.Description = TranslateService.GetTranslate("",$@"Message_base_message_{info.Name}",info.Description);
                        dataRow[info.Description] = item.GetPropertyValue(info.Name).IsNullOrEmpty() ? DBNull.Value : item.GetPropertyValue(info.Name);
                    }
                }
                dt.Rows.Add(dataRow);
            }
            return dt;
        }

        public base_message GetTheData(string id)
        {
            return GetEntity(id);
        }

        public List<base_message> GetTheData2(string id)
        {
            string sql = string.Format(@"select* from base_message c  where c.Mid = '0'", id);
           return GetListBySql<base_message>(sql);
        }

        [DataValidateAttribute()]
        public AjaxResult AddData(base_message newData)
        {
            Insert(newData);

            return Success();
        }

        [DataValidateAttribute()]
        public AjaxResult AddData(List<base_message> newData)
        {
            Insert(newData);

            return Success();
        }


        public AjaxResult OCRData(string FileName)
        {
            var res = Treca.Util.Helper.AiHelper.Ocr(FileName);
            if (res == "ok")
            {
                return Success();
            }
            else
            {
                return Error();
            }
        }

        [DataValidateAttribute()]
        public AjaxResult UpdateData(base_message theData)
        {
            Update(theData);

            return Success();
        }

        public AjaxResult DeleteData(List<string> ids)
        {
            Delete(ids);

            return Success();
        }
        
        
        public AjaxResult ApproveData(List<string> ids)
        {
            foreach(string id in ids) {
                base_message date = GetTheData(id);
                ApproveData(date);
            }
            return Success();
        }
        public AjaxResult ReverseData(List<string> ids)
        {
            foreach(string id in ids) {
                base_message date = GetTheData(id);
                ReverseData(date);
            }
            return Success();
        }
        #endregion

        #region 私有成员

        #endregion

        #region 数据模型

        #endregion
    }
}